﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.SqlTypes;
using System.IO;

namespace SqlServerDataExporter
{
    public class SqlDataExporter
    {

        private SqlConnection _connection;

        /// <summary>
        /// Connect to the database and opens the connection.
        /// </summary>
        /// <param name="connectionString"></param>
        public void Connect(string connectionString)
        {
            try
            {
                _connection = new SqlConnection(connectionString);
                _connection.Open();
            }
            catch (Exception e)
            {
               _connection = null;
               throw e;                
            }           
        }


        /// <summary>
        /// Returns a list of tables in the database.
        /// </summary>
        /// <returns></returns>
        public IList<TableItem> ListTables()
        {
            List<TableItem> tables = new List<TableItem>();
            DataTable dt = _connection.GetSchema(SqlClientMetaDataCollectionNames.Tables);
            foreach (DataRow row in dt.Rows)
            {
                string databsename = (string)row[0];
                string schemaname = (string) row[1];
                string tablename  = (string) row[2];

                TableItem item = new TableItem
                {
                    Database = databsename,
                    Schema = schemaname,
                    Table = tablename
                };

                tables.Add(item);
            }
            tables = tables.OrderBy(x => x.Table).ToList();
            return tables;
        }


        /// <summary>
        /// Creates a connectionstring.
        /// </summary>
        /// <param name="server"></param>
        /// <param name="database"></param>
        /// <param name="user"></param>
        /// <param name="password"></param>
        /// <param name="windowsAuth"></param>
        /// <returns></returns>
        public string CreateConnectionString(string server, string database, string user, string password, bool windowsAuth)
        {           
            if (windowsAuth)
            {
                return string.Format("Server={0};Database={1};Trusted_Connection=true;", server, database);
            }
            else
            {
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

                if (!string.IsNullOrEmpty(database))
                {
                    builder.InitialCatalog = database;
                }
                builder.IntegratedSecurity = false;

                if (!string.IsNullOrEmpty(user))
                {
                    builder.UserID = user;
                }

                if (!string.IsNullOrEmpty(password))
                {
                    builder.Password = password;
                }

                if (!string.IsNullOrEmpty(server))
                {
                    builder.DataSource = server;
                }
                return builder.ConnectionString;
            }

        }



        /// <summary>
        /// Exports the tables to a stream as INSERT SQL statements.
        /// </summary>
        /// <param name="tables"></param>
        /// <param name="stream"></param>
        /// <param name="rp"></param>
        public void ExportTable(IList<TableItem> tables, Stream stream, IReportProgress rp)
        {
            StreamWriter writer = new StreamWriter(stream);          
            foreach (TableItem item in tables)
            {
                if(rp.DoCancel)
                    return;                
                if (item.Selected)
                {
                    ExportTable(item, writer, rp);
                }
            }
            writer.Flush();          
        }



        /// <summary>
        /// Gets all supported columns of a table.
        /// </summary>
        /// <param name="table"></param>
        /// <returns>List of columns ordered by name</returns>
        public IList<Column> GetColumns(TableItem table)
        {
            IList<Column> columns = new List<Column>();

            string[] restrictions = 
                {
                    null, //Katalog
                    null, //Besitzer
                    table.Table, //Tabelle
                    null  //Tabellentyp
                };
            DataTable dt = _connection.GetSchema(SqlClientMetaDataCollectionNames.Columns, restrictions);
            foreach (DataRow row in dt.Rows)
            {
                string colName = (string)row[3];
                string colType = (string)row[7];

                Column col = new Column();

                col.ColumnName = colName;
                col.SqlType = colType;

                switch (colType)
                {
                    case "int":
                    case "money":
                    case "tinyint":
                    case "decimal":
                    case "float":
                    case "real":
                        col.InternalType = ColumnType.NUMBER;
                        break;
                    case "nvarchar":
                    case "varchar":
                    case "nchar":
                    case "char":
                    case "text":
                    case "ntext":
                        col.InternalType = ColumnType.CHAR;
                        break;
                    case "datetime":
                    case "datetime2":
                    case "smalldatetime":
                        col.InternalType = ColumnType.DATETIME;
                        break;
                    case "varbinary":
                    case "binary":
                        col.InternalType = ColumnType.BINARY;
                        break;
                    case "uniqueidentifier":
                        col.InternalType = ColumnType.GUID;
                        break;
                    default:
                        continue;
                }

                columns.Add(col);
            }

            columns = columns.OrderBy(x => x.ColumnName).ToList();

            return columns;
        }


        /// <summary>
        /// Exports a whole table to a file as insert statements.
        /// </summary>
        /// <param name="item"></param>
        /// <param name="writer"></param>
        /// <param name="rp"></param>
        private void ExportTable(TableItem item, StreamWriter writer, IReportProgress rp)
        {
            writer.WriteLine("-- SET IDENTITY_INSERT {0} ON", item.Table);
            IList<Column> columns = item.Columns.Where(x => x.Selected).ToList();       
            string sql = GetSelectStatement(item, columns);
            SqlCommand command = new SqlCommand(sql, _connection);
            using(SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    if(rp.DoCancel)
                    {
                        return;
                    }
                    string insertStatement = CreateExportTableSql(reader, writer, item, columns);
                    writer.WriteLine(insertStatement);
                    rp.DoReportNextItem();
                }
            }
            writer.WriteLine("-- SET IDENTITY_INSERT {0} OFF", item.Table);
        }




        /// <summary>
        /// Creates an insert statement for a row.
        /// </summary>
        /// <param name="reader"></param>
        /// <param name="writer"></param>
        /// <param name="item"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        private string CreateExportTableSql(SqlDataReader reader, StreamWriter writer, TableItem item, IList<Column> columns)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("INSERT INTO [");
            sb.Append(item.Schema);
            sb.Append("].[");
            sb.Append(item.Table);
            sb.Append("] (");

            //List Column Names
            for (int i = 0; i < columns.Count; i++)
            {
                string colName = columns[i].ColumnName;
                sb.Append(colName);

                if (i < columns.Count - 1)
                {
                    sb.Append(", ");
                }
            }


            sb.Append(") VALUES (");


            //List Column Values
            for (int i = 0; i < columns.Count; i++)
            {
                ColumnType type = columns[i].InternalType;
                string val = PrintValue(i, reader, type);
                sb.Append(val);

                if (i < columns.Count - 1)
                {
                    sb.Append(", ");
                }
            }


            sb.Append(")");     
            return sb.ToString();
        }


        /// <summary>
        ///  Prints the value of a cell of a row.
        /// </summary>
        /// <param name="col"></param>
        /// <param name="reader"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        private string PrintValue(int col, SqlDataReader reader, ColumnType type)
        {

            StringBuilder sb = new StringBuilder();
            dynamic value = reader.GetSqlValue(col);
            if (value.IsNull)
            {
                sb.Append("NULL");
            }
            else
            {
                if (type == ColumnType.CHAR)
                {                    
                    sb.Append("'");
                    string text = value.Value;
                    text = text.Replace("'", "''");
                    sb.Append(text);
                    sb.Append("'");
                }
                else if (type == ColumnType.DATETIME)
                {
                    sb.Append("'");
                    DateTime datetime = value.Value;
                    sb.Append(datetime.ToString("yyyy-MM-dd HH:mm:ss.fff"));//YYYYMMddHHmmss
                    sb.Append("'");
                }
                else if (type == ColumnType.BINARY)
                {
                    byte[] array = value.Value;
                    string asString = HexStringConverter.ByteArrayToString(array);
                    sb.Append("0x");
                    sb.Append(asString);
                }
                else if (type == ColumnType.GUID)
                {
                    Guid x = value.Value;
                    sb.Append("'");
                    sb.Append(x);
                    sb.Append("'");
                }
                else
                {
                    try
                    {
                        sb.Append(value.Value);
                    }
                    catch (OverflowException)
                    {
                        sb.Append("overflow");
                    }
                }
                               
            }         
            return sb.ToString();
        }


        /// <summary>
        /// Creates the SQL select statement.
        /// </summary>
        /// <param name="item"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        private string GetSelectStatement(TableItem item, IList<Column> columns)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ");

            for (int i = 0; i < columns.Count; i++)
            {
                Column col = columns[i];
                sb.Append(col.ColumnName);
                if (i < columns.Count - 1)
                {
                    sb.Append(", ");
                }
            }
           

            sb.Append(" FROM ");
            sb.Append(item.Table);

            return sb.ToString();
        }
    }
}
